package com.apes.fn.controlWaitDisposal;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.jpa.Connector;
import com.apes.framework.query.ApesDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

/**
 * @program: apes-server
 * @description
 * @author: czs
 * @create: 2021-05-20 15:30
 **/
@Component
public class ControlWaitDisposalDao extends ApesDao {
    @Autowired
    Connector connector;

    public JSONArray findWaitSubmission(JSONObject param) {
        StringBuffer sf = new StringBuffer();
        sf.append(" SELECT A.ID,A.SALES_AMOUNT,TO_CHAR(A.CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS') CREATE_DATE,A.CAR_LICENSE,A.SERVE_TYPE_NAME, ");
        sf.append(" DECODE(A.STATE,'create','新增','techAudit','技术审核完成','backTechAudit','技术审核退回','priceAudit','价格审核完成','backPriceAudit','价格审核退回','inquiryFinish','询价完成','backInquiry','询价退回','') STATE");
        sf.append(" FROM FN_SERVICE_WAIT_SUBMISSION A");
        sf.append(" WHERE A.BELONG_DEPT_ID = ");
        sf.append("  '"+param.getJSONObject("data").getString("dept_id")+ "'");
        sf.append(" AND A.VALID =1 ORDER BY PLAN_CREATE_DATE DESC");
/*
        sf.append(" SELECT A.ID,A.SALES_AMOUNT,TO_CHAR(A.CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS') CREATE_DATE, ");
        sf.append(" DECODE(A.STATE,'create','新增','techAudit','技术审核完成','backTechAudit','技术审核退回','priceAudit','价格审核完成','backPriceAudit','价格审核退回','inquiryFinish','询价完成','backInquiry','询价退回','') STATE,");
        sf.append(" (SELECT E.CAR_LICENSE FROM FN_CUSTOMER_EXPAND E WHERE E.ID = A.FN_CUSTOMER_EXPAND_ID) CAR_LICENSE,");
        sf.append(" (SELECT REGEXP_REPLACE(LISTAGG((SELECT S.NAME FROM mas_serve_type S WHERE S.ID = I.SERVE_TYPE_ID),',') WITHIN GROUP(ORDER BY I.SERVE_TYPE_ID),'([^,]+)(,\\1)*(,|$)','\\1\\3')" +
                " FROM SER_SERVICE_PLAN_ITEM I WHERE I.SERVICE_PLAN_ID = A.ID) SERVE_TYPE_ID");
        sf.append(" FROM SER_SERVICE_PLAN A  ");
        sf.append(" WHERE A.BELONG_DEPT_ID = ");
        sf.append(" '"+param.getJSONObject("data").getString("dept_id")+ "'");
        sf.append(" AND A.STATE IN('create','techAudit','backTechAudit','priceAudit','backPriceAudit','inquiryFinish','backInquiry')");
*/
        return toJSONArray(find(sf.toString(), param));
    }

    public JSONArray findAllWaitReceive(JSONObject param) {
        StringBuffer sf = new StringBuffer();
        sf.append(" SELECT A.ID,DECODE(A.STATE,'confirm','确认','qualityTest','质检','') STATE,(B.SALE_QTY-B.QTY_END) QTY_SALE,B.QTY_RECEIVE, ");
        sf.append(" (SELECT E.CAR_LICENSE FROM FN_CUSTOMER_EXPAND E WHERE E.ID = A.FN_CUSTOMER_EXPAND_ID) CAR_LICENSE,");
        sf.append(" (SELECT L.NAME FROM MAS_LOCATION L WHERE L.ID = B.LOCATION_ID) LOCATION");
        sf.append(" FROM SER_SERVICE_PLAN A,SER_SERVICE_PLAN_ITEM B  ");
        sf.append(" WHERE a.STATE in('confirm','qualityTest') AND A.BELONG_DEPT_ID =  ");
        sf.append(" '"+param.getJSONObject("data").getString("dept_id")+ "'");
        sf.append(" AND A.ID = B.SERVICE_PLAN_ID AND (b.SALE_QTY - b.QTY_END) <> 0 AND (SELECT d.PRODUCT_GROUP_ID FROM MAS_PRODUCT d where d.id = B.PRODUCT_ID) <>10");
        return toJSONArray(find(sf.toString(), param));
    }
}
